<%@ Page language="vb" CodeFile="searchPartner.aspx.vb" Inherits="searchPartner_aspx_vb" %>

<%
    rowNumber = 0
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        partnerID = Request.QueryString("partnerID")
        If Len(partnerID) > 0 Then 
            partnerID = CLng(partnerID)
            dbOpen()
            RecSet = Conn.Execute("Select c.countryCurrency, (Select Count(d.dealerID) From tblDealer d Where d.partnerID = p.partnerID) As dealerCount, (Select Count(v.vehicleID) From tblVehicle v Where v.partnerID = p.partnerID) As vehicleCount, (Select Sum(i.invoiceAmount) From tblInvoice i Where i.partnerID = p.partnerID) As invoiceSum, (Select Count(c.claimID) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.partnerID = p.partnerID) As claimCount, (Select Sum(c.paymentAmount) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.partnerID = p.partnerID) As claimSum From (tblPartner p INNER JOIN tblCountry c ON c.countryID = p.countryID) Where p.partnerID = " & partnerID & "")
            If Not RecSet.EOF Then 
                countryCurrency = RecSet.Fields("countryCurrency").Value
                dealerCount = RecSet.Fields("dealerCount").Value
                vehicleCount = RecSet.Fields("vehicleCount").Value
                invoiceSum = RecSet.Fields("invoiceSum").Value
                claimCount = RecSet.Fields("claimCount").Value
                claimSum = RecSet.Fields("claimSum").Value
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            If IsDBNull(invoiceSum) Then 
                invoiceSum = 0
            End IF
            If IsDBNull(claimSum) Then 
                claimSum = 0
            End IF
        End IF
        Over()
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH PARTNER<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchPartner.aspx?subDo=search' method='post'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID'>")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td><div id='error_countryID' class='edit_error'>&laquo; OBS!</div></td>")
        Response.Write("</tr>")
        Response.Write("<tr><td class='formBold'>Partner:</td><td align='right'><input type='text' name='partnerName' id='partnerName' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>PartnerID:</td><td align='right'><input type='text' name='partnerID' id='partnerID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Street:</td><td align='right'><input type='text' name='street' id='street' size='40'maxlength='50' ></td></tr>")
        Response.Write("<tr><td class='formBold'>Zipcode:</td><td align='right'><input type='text' name='zipCode' id='zipCode' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>City:</td><td align='right'><input type='text' name='city' id='city' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        If Len(partnerID) > 0 Then 
            Response.Write("<tr><td class='headline' colspan='2'>PARTNER STATISTICS<hr class='hrHeadline'></td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Number of dealers:</td><td align='right'>" & dealerCount & "</td></tr>")
            Response.Write("<tr><td>Number of lease contracts:</td><td align='right'>" & vehicleCount & "</td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Number of claims reported:</td><td align='right'>" & claimCount & "</td></tr>")
            Response.Write("<tr><td>Total invoiced amount (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(invoiceSum), 2) & "</td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Total claim cost (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(claimSum), 2) & "</td></tr>")
            Response.Write("<tr><td colspan='2'><hr class='hrHeadline'></td></tr>")
        End IF
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
        Response.Write("<tr><td class='headline' colspan='14'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
        countryID = Request.Form("countryID")
        partnerName = Request.Form("partnerName")
        partnerID = Request.Form("partnerID")
        street = Request.Form("street")
        zipcode = Request.Form("zipCode")
        city = Request.Form("city")
        If Len(partnerID) = 0 Then 
            partnerID = Request.QueryString("partnerID")
        End IF
        If Len(countryID) > 0 Then 
            sqlCountryID = " And p.countryID = " & countryID & ""
        End IF
        If Len(partnerName) > 0 Then 
            partnerName = Trim(partnerName)
            partnerName = Replace(CStr(partnerName), "'", "")
            sqlPartnerName = " And p.partnerName Like '%" & partnerName & "%'"
        End IF
        If Len(partnerID) > 0 Then 
            sqlPartnerID = " And p.partnerID = " & partnerID & ""
        End IF
        If Len(street) > 0 Then 
            street = Trim(street)
            street = Replace(CStr(street), "'", "")
            sqlStreet = " And p.street Like '%" & street & "%'"
        End IF
        If Len(zipcode) > 0 Then 
            sqlZipcode = " And p.zipcode Like '%" & zipcode & "%'"
        End IF
        If Len(city) > 0 Then 
            city = Trim(city)
            city = Replace(CStr(city), "'", "")
            sqlCity = " And p.city Like '%" & city & "%'"
        End IF
        If Len(subDo) = 0 Then 
            sqlCountryID = " And p.countryID = 1000"
        End IF
        dbOpen()
        RecSet = Conn.Execute("Select p.partnerID, p.partnerName, p.street, p.zipCode, p.city, p.pwd, p.chooseClient, p.invoiceFee, p.contractNumberPrefix, c.country From (tblPartner p INNER JOIN tblCountry c ON c.countryID = p.countryID) Where p.partnerID > 0" & sqlCountryID & sqlPartnerName & sqlPartnerID & sqlStreet & sqlZipcode & sqlCity & " Order By p.partnerName")
        If RecSet.EOF Then 
            Response.Write("<tr><td><font color='red'>No partners found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Partner</td>")
            Response.Write("<td class='formBold'>Street</td>")
            Response.Write("<td class='formBold'>Zip code</td>")
            Response.Write("<td class='formBold'>City</td>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'>Username</td>")
            Response.Write("<td class='formBold'>Password</td>")
            Response.Write("<td class='formBold'>Invoice fee</td>")
            Response.Write("<td class='formBold'>Contract Number Prefix</td>")
            Response.Write("<td class='formBold'>Choose Lease Taker</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
            Do Until RecSet.EOF
                
                partnerID = RecSet.Fields("partnerID").Value
                partnerName = RecSet.Fields("partnerName").Value
                street = RecSet.Fields("street").Value
                zipcode = RecSet.Fields("zipCode").Value
                city = RecSet.Fields("city").Value
                pwd = RecSet.Fields("pwd").Value
                chooseClient = RecSet.Fields("chooseClient").Value
                invoiceFee = RecSet.Fields("invoiceFee").Value
                
                If Not IsDBNull(RecSet.Fields("contractNumberPrefix").Value) Then
                    contractNumberPrefix = RecSet.Fields("contractNumberPrefix").Value
                End If
            
                country = RecSet.Fields("country").Value
                
                If CInt(chooseClient) = 1 Then
                    chooseClient = "<font color='green'>Yes</font>"
                Else
                    chooseClient = "<font color='red'>No</font>"
                End If
                
                If rowNumber = 0 Then
                    strGrey = ""
                    rowNumber = 1
                Else
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 0
                End If
                
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & partnerName & "</td>")
                Response.Write("<td>" & street & "</td>")
                Response.Write("<td>" & zipcode & "</td>")
                Response.Write("<td>" & city & "</td>")
                Response.Write("<td>" & country & "</td>")
                Response.Write("<td>p" & partnerID & "</td>")
                Response.Write("<td>" & pwd & "</td>")
                Response.Write("<td>" & invoiceFee & "</td>")
                Response.Write("<td>" & contractNumberPrefix & "</td>")
                Response.Write("<td>" & chooseClient & "</td>")
                Response.Write("<td align='right'><a href='editPartner.aspx?partnerID=" & partnerID & "'>edit partner</a></td>")
                Response.Write("<td align='right'><a href='searchPartner.aspx?subDo=viewInvoices&partnerID=" & partnerID & "'>view invoices</a></td>")
                Response.Write("</tr>")
                
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='14'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        If subDo = "viewInvoices" Or subDo = "invoiceContent" Then 
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
            Response.Write("<tr><td class='headline' colspan='4'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
            partnerID = CLng(partnerID)
            sqlPartnerID = " And i.partnerID = " & partnerID & ""
            dbOpen()
            RecSet = Conn.Execute("Select i.invoiceID, i.invoiceDate, i.invoicePrinted, i.invoiceSettled, c.country From (tblInvoice i INNER JOIN tblCountry c ON c.countryID = i.countryID) Where i.invoiceID > 0" & sqlPartnerID & " Order By i.invoiceID")
            If RecSet.EOF Then 
                Response.Write("<tr><td><font color='red'>No invoices found.</font></td></tr>")
            Else
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Invoice ID</td>")
                Response.Write("<td class='formBold'>Status</td>")
                Response.Write("<td class='formBold'>Country</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                Do Until RecSet.EOF
                    invoiceID = RecSet.Fields("invoiceID").Value
                    country = RecSet.Fields("country").Value
                    invoiceDate = RecSet.Fields("invoiceDate").Value
                    invoicePrinted = RecSet.Fields("invoicePrinted").Value
                    invoiceSettled = RecSet.Fields("invoiceSettled").Value
                    If IsDBNull(invoiceDate) Then 
                        invoiceStatus = "<font color='red'>not activated</font>"
                    ElseIf CInt(invoicePrinted) = 0 Then 
                        invoiceStatus = "queued for printing"
                    ElseIf CInt(invoicePrinted) = 1 Then 
                        invoiceStatus = "printed"
                    ElseIf CInt(invoiceSettled) = 1 Then 
                        invoiceStatus = "<font color='green'>settled</font>"
                    End IF
                    If rowNumber = 0 Then 
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 1
                    Else
                        strGrey = ""
                        rowNumber = 0
                    End IF
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & invoiceID & "</td>")
                    Response.Write("<td>" & invoiceStatus & "</td>")
                    Response.Write("<td>" & country & "</td>")
                    Response.Write("<td align='right'><a href='searchPartner.aspx?subDo=invoiceContent&partnerID=" & partnerID & "&invoiceID=" & invoiceID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='4'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End IF
        Response.Write("<tr><td><br></td></tr>")
        If subDo = "invoiceContent" Then 
            invoiceID = Request.QueryString("invoiceID")
            invoiceID = CLng(invoiceID)
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
            Response.Write("<tr><td class='headline' colspan='12'>LEASE INVOICE DETAILS FOR INVOICE ID " & invoiceID & "<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select i.invoiceID, i.invoiceAmount, i.invoiceClaim, i.invoiceDate, i.paymentDate, i.paymentTerm, i.invoiceSettled, i.invoicePrinted, i.date, c.country, countryCurrency, u.username From ((tblInvoice i INNER JOIN tblCountry c ON c.countryID = i.countryID) LEFT JOIN tblUser u ON u.userID = i.userID) Where i.invoiceID = " & invoiceID & "")
            If RecSet.EOF Then 
                Response.Write("<tr><td><font color='red'>ERROR</font></td></tr>")
            Else
                invoiceID = RecSet.Fields("invoiceID").Value
                invoiceAmount = RecSet.Fields("invoiceAmount").Value
                invoiceClaim = RecSet.Fields("invoiceClaim").Value
                invoiceDate = RecSet.Fields("invoiceDate").Value
                paymentDate = RecSet.Fields("paymentDate").Value
                paymentTerm = RecSet.Fields("paymentTerm").Value
                invoiceSettled = RecSet.Fields("invoiceSettled").Value
                invoicePrinted = RecSet.Fields("invoicePrinted").Value
                __date = RecSet.Fields("date").Value
                country = RecSet.Fields("country").Value
                countryCurrency = RecSet.Fields("countryCurrency").Value
                username = RecSet.Fields("username").Value
                If CInt(invoiceSettled) = 0 Then 
                    invoiceSettled = "<font color='red'>No</font>"
                Else
                    invoiceSettled = "<font color='green'>Yes</font>"
                End IF
                If CInt(invoicePrinted) = 0 Then 
                    invoicePrinted = "<font color='red'>No</font>"
                Else
                    invoicePrinted = "<font color='green'>Yes</font>"
                End IF
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Invoice ID</td>")
                Response.Write("<td class='formBold'>Amount (" & countryCurrency & ")</td>")
                Response.Write("<td class='formBold'>Claim (" & countryCurrency & ")</td>")
                Response.Write("<td class='formBold'>Date of invoice</td>")
                Response.Write("<td class='formBold'>Date of payment</td>")
                Response.Write("<td class='formBold'>Payment term (days)</td>")
                Response.Write("<td class='formBold'>Settled</td>")
                Response.Write("<td class='formBold'>Printed</td>")
                Response.Write("<td class='formBold'>Referee</td>")
                Response.Write("<td class='formBold'>First created</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                Response.Write("<tr>")
                Response.Write("<td>" & invoiceID & "</td>")
                Response.Write("<td>" & invoiceAmount & "</td>")
                Response.Write("<td>" & invoiceClaim & "</td>")
                Response.Write("<td>" & invoiceDate & "</td>")
                Response.Write("<td>" & paymentDate & "</td>")
                Response.Write("<td>" & paymentTerm & "</td>")
                Response.Write("<td>" & invoiceSettled & "</td>")
                Response.Write("<td>" & invoicePrinted & "</td>")
                Response.Write("<td>" & username & "</td>")
                Response.Write("<td>" & Left(__date, 10) & "</td>")
                If CDbl(invoiceAmount) = 0 Then 
                    Response.Write("<td></td>")
                Else
                    Response.Write("<td><a href='printInvoice.aspx?invoiceID=" & invoiceID & "' target='_blank'>print</td>")
                End IF
                Response.Write("<td><a href='editInvoice.aspx?invoiceID=" & invoiceID & "'>view details</td>")
                Response.Write("</tr>")
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='100%'>")
            Response.Write("<tr><td class='headline' colspan='9'>THIS INVOICE CONTAINS THE FOLLOWING VEHICLES<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.date, p.product, pl.price, b.brand, m.model, d.dealer From ((((((tblInvoiceContent ic INNER JOIN tblVehicle v ON v.vehicleID = ic.vehicleID) INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblPriceList pl ON pl.priceListID = v.priceListID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblDealer d ON d.dealerID = v.dealerID) Where ic.invoiceID = " & invoiceID & "")
            If RecSet.EOF Then 
                Response.Write("<tr><td><font color='red'>ERROR</font></td></tr>")
            Else
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Car dealer</td>")
                Response.Write("<td class='formBold'>Contract number</td>")
                Response.Write("<td class='formBold'>Licence number</td>")
                Response.Write("<td class='formBold'>VIN-number</td>")
                Response.Write("<td class='formBold'>Product</td>")
                Response.Write("<td class='formBold'>Price (" & countryCurrency & ")</td>")
                Response.Write("<td class='formBold'>Car make</td>")
                Response.Write("<td class='formBold'>First created</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                rowNumber = 1
                Do Until RecSet.EOF
                    vehicleID = RecSet.Fields("vehicleID").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    contractNumber = RecSet.Fields("contractNumber").Value
                    __date = RecSet.Fields("date").Value
                    product = RecSet.Fields("product").Value
                    price = RecSet.Fields("price").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    dealer = RecSet.Fields("dealer").Value
                    If rowNumber = 0 Then 
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 1
                    Else
                        strGrey = ""
                        rowNumber = 0
                    End IF
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & dealer & "</td>")
                    Response.Write("<td>" & contractNumber & "</td>")
                    Response.Write("<td>" & regnr & "</td>")
                    Response.Write("<td>" & vinNumber & "</td>")
                    Response.Write("<td>" & product & "</td>")
                    Response.Write("<td>" & price & "</td>")
                    Response.Write("<td>" & brand & " " & model & "</td>")
                    Response.Write("<td>" & Left(__date, 10) & "</td>")
                    Response.Write("<td><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='9'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End IF
        Response.Write("</table>")
        Under()
    End IF
%>
